/***
This do-file compares the tracker employment data to the employment according to
the Current Employment Statistics.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
project, uses("${root}/code/set_globals.do")
include "${root}/code/set_globals.do"
local category "Employment"

* Create required subfolders
cap mkdir "${root}/data/derived/CES"
cap mkdir "${root}/results/`category'"
cap mkdir "${root}/results/paper numbers"
cap mkdir "${root}/results/paper numbers/`category'"

*-------------------------------------------------------------------------------
* Load and clean tracker data
*-------------------------------------------------------------------------------

project, uses("${root}/data/web/data/Employment - State - Weekly.csv")
import delimited "${root}/data/web/data/Employment - State - Weekly.csv", clear

* Create date
gen date = mdy(month, day_endofweek, year)
format date %d

 keep date emp statefips

 replace emp = emp * 100 
 
 rename emp emp_combined
 
*-------------------------------------------------------------------------------
* Open CES by State
*-------------------------------------------------------------------------------

preserve 
project, uses("${root}/data/derived/CES/state_monthly_total_employment.dta")
use "${root}/data/derived/CES/state_monthly_total_employment.dta", clear
gisid year month statefips

gen date = mdy(month, 15, year)
format %td date
gen temp = employment if date == td(15jan2020)
bys statefips: gegen base_CES = mean(temp)
gen norm_employment_CES = (employment / base_CES - 1) * 100
keep date statefips norm_employment_CES base_CES

gen Fridays = date - dow(date) + 5
format Fridays %td
drop date 
rename Fridays date

tempfile ces_state
save `ces_state'

restore

merge 1:1 date statefips using `ces_state'

label define statefips 1 AL 2 AK 4 AZ 5 AR 6 CA 8 CO 9 CT 10 DE 11 DC ///
12 FL 13 GA 15 HI 16 ID 17 IL 18 IN 19 IA 20 KS 21 KY 22 LA 23 ME 24 ///
MD 25 MA 26 MI 27 MN 28 MS 29 MO 30 MT 31 NE 32 NV 33 NH 34 NJ 35 NM ///
36 NY 37 NC 38 ND 39 OH 40 OK 41 OR 42 PA 44 RI 45 SC 46 SD 47 TN 48 ///
TX 49 UT 50 VT 51 VA 53 WA 54 WV 55 WI 56 WY 60 AS 66 GU 72 PR 78 VI

label val statefips statefips

rename statefips state_fips

project, uses("${root}/data/derived/ACS 2014-2018 5-Year State/ACS 2014-2018 State.dta")
merge m:1 state_fips using "${root}/data/derived/ACS 2014-2018 5-Year State/ACS 2014-2018 State.dta", nogen keep(matched master) ////
	keepusing(pop_2014_2018)
	
rename (state_fips pop_2014_2018)(statefips population)

keep if emp_combined != . & norm_employment_CES != . 

gen period = 1 if date == td(17jul2020)
replace period = 2 if date == td(16jul2021)

label define period_lab  1 "July 2020" 2 "July 2021"

label values period period_lab

keep if period != .
	
** July 2020 stats
corr emp_combined norm_employment_CES [w = population] if date == td(17jul2020)
local corr_july_2020 : di %03.2f `r(rho)'

cap drop error error_sq
gen error = emp_combined - norm_employment_CES
gen error_sq = error ^ 2
sum error_sq [w=population] if date == td(17jul2020)
local rmse_july_2020 : di %4.2f sqrt(`r(mean)')

** July 2021 stats
corr emp_combined norm_employment_CES [w = population] if date == td(16jul2021)
local corr_july_2021 : di %03.2f `r(rho)'

cap drop error error_sq
gen error = emp_combined - norm_employment_CES
gen error_sq = error ^ 2
sum error_sq [w=population] if date == td(16jul2021)
local rmse_july_2021 : di %4.2f sqrt(`r(mean)')

*-------------------------------------------------------------------------------
* Plot
*-------------------------------------------------------------------------------

tw (lfit emp_combined norm_employment_CES [w = population] if period == 1, lcolor(oi2)) ///
(scatter emp_combined norm_employment_CES if period == 1, mlabel(statefips) msymbol(i) mlabpos(0) mlabcolor(oi1)), ///
 ///
xscale(range(-20 -4)) xlabel(-20 "-20%" -15 "-15%" -10 "-10%" -5 "-5%" 0 "0%") ///
ylabel(-30 "-30%" -20 "-20%" -10 "-10%" 0 "0%", nogrid) ///
legend(off) ///
ytitle("Paychex-Intuit Change in Employment (%)" "from January to July 2020") xtitle("CES Change in Employment (%)" "from January to July 2020") ///
text(-28 -4 "Correlation: `corr_july_2020'" "RMSE: `rmse_july_2020' p.p.", color(oi2) placement(east) justification(left)) ///
${title_`version'}


oi_graph_export "${root}/results/Employment/Employment Tracker vs CES by State", type(${fig_type})

*-------------------------------------------------------------------------------
* Export numbers for paper
*-------------------------------------------------------------------------------

** July 2020
cap erase "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2020 by State Paychex-Intuit-Earnin vs CES.yaml"

yamlout using "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2020 by State Paychex-Intuit-Earnin vs CES.yaml", ///
	key("emp_ces_state_corr_2020") ///
	comment("Correlation") ///
	value(`corr_july_2020') fmt(%9.2f)
yamlout using "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2020 by State Paychex-Intuit-Earnin vs CES.yaml", ///
	key("emp_ces_state_rmse_2020") ///
	comment("RMSE") ///
	value(`rmse_july_2020') fmt(%9.2f)

project, creates("${root}/results/paper numbers/`category'/Change in Employment Rates to July 2020 by State Paychex-Intuit-Earnin vs CES.yaml")

** July 2021
cap erase "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2021 by State Paychex-Intuit-Earnin vs CES.yaml"

yamlout using "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2021 by State Paychex-Intuit-Earnin vs CES.yaml", ///
	key("emp_ces_state_corr_2021") ///
	comment("Correlation") ///
	value(`corr_july_2021') fmt(%9.2f)
yamlout using "${root}/results/paper numbers/`category'/Change in Employment Rates to July 2021 by State Paychex-Intuit-Earnin vs CES.yaml", ///
	key("emp_ces_state_rmse_2021") ///
	comment("RMSE") ///
	value(`rmse_july_2021') fmt(%9.2f)
	
project, creates("${root}/results/paper numbers/`category'/Change in Employment Rates to July 2021 by State Paychex-Intuit-Earnin vs CES.yaml")
